{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "3353d487",
   "metadata": {},
   "source": [
    "### pandas数据结构\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a4ad7cf",
   "metadata": {},
   "source": [
    "#### Series[一维]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "59b2e6af",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "75ac2150",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "A     92.0\n",
       "B    119.0\n",
       "C    118.0\n",
       "D      7.0\n",
       "E      9.0\n",
       "F     39.0\n",
       "H     41.0\n",
       "I     18.0\n",
       "J     34.0\n",
       "K     29.0\n",
       "Name: Python, dtype: float16"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s = pd.Series(data = np.random.randint(0,150,size = 10),\n",
    "              dtype=np.float16,\n",
    "              index = list('ABCDEFHIJK'),name = 'Python')\n",
    "s\n",
    "# Series 更加结构化。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "e5aaf308",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 92., 119., 118.,   7.,   9.,  39.,  41.,  18.,  34.,  29.],\n",
       "      dtype=float16)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s.values # 获取的数据，就是NumPy数组"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "790e4fb2",
   "metadata": {},
   "source": [
    "#### DataFrame[二维] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "22fbd6f8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Python</th>\n",
       "      <th>Math</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>30</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>80</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100</td>\n",
       "      <td>149</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Python  Math\n",
       "0      30   100\n",
       "1      80    20\n",
       "2     100   149"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({'Python':[30,80,100],'Math':[100,20,149]})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "3101b090",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Python</th>\n",
       "      <th>Math</th>\n",
       "      <th>En</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <td>130</td>\n",
       "      <td>133</td>\n",
       "      <td>106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <td>54</td>\n",
       "      <td>28</td>\n",
       "      <td>126</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C</th>\n",
       "      <td>48</td>\n",
       "      <td>29</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>D</th>\n",
       "      <td>94</td>\n",
       "      <td>45</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>E</th>\n",
       "      <td>94</td>\n",
       "      <td>122</td>\n",
       "      <td>149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>F</th>\n",
       "      <td>30</td>\n",
       "      <td>41</td>\n",
       "      <td>139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>H</th>\n",
       "      <td>33</td>\n",
       "      <td>110</td>\n",
       "      <td>106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>I</th>\n",
       "      <td>118</td>\n",
       "      <td>123</td>\n",
       "      <td>70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>J</th>\n",
       "      <td>24</td>\n",
       "      <td>93</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>K</th>\n",
       "      <td>139</td>\n",
       "      <td>8</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Python  Math   En\n",
       "A     130   133  106\n",
       "B      54    28  126\n",
       "C      48    29   52\n",
       "D      94    45   35\n",
       "E      94   122  149\n",
       "F      30    41  139\n",
       "H      33   110  106\n",
       "I     118   123   70\n",
       "J      24    93   72\n",
       "K     139     8   56"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = pd.DataFrame(data = np.random.randint(0,150,size = (10,3)),\n",
    "                   index = list('ABCDEFHIJK'),# 行索引\n",
    "                   columns=['Python','Math','En']) # 列索引\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "f6ef2ffe",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Python</th>\n",
       "      <th>Math</th>\n",
       "      <th>En</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>49</td>\n",
       "      <td>53</td>\n",
       "      <td>137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>82</td>\n",
       "      <td>86</td>\n",
       "      <td>106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>68</td>\n",
       "      <td>27</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>91</td>\n",
       "      <td>48</td>\n",
       "      <td>122</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>107</td>\n",
       "      <td>18</td>\n",
       "      <td>145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>995</th>\n",
       "      <td>139</td>\n",
       "      <td>64</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>996</th>\n",
       "      <td>103</td>\n",
       "      <td>30</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>997</th>\n",
       "      <td>121</td>\n",
       "      <td>147</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>998</th>\n",
       "      <td>145</td>\n",
       "      <td>4</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999</th>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>119</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1000 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Python  Math   En\n",
       "0        49    53  137\n",
       "1        82    86  106\n",
       "2        68    27   93\n",
       "3        91    48  122\n",
       "4       107    18  145\n",
       "..      ...   ...  ...\n",
       "995     139    64   11\n",
       "996     103    30    2\n",
       "997     121   147   44\n",
       "998     145     4   36\n",
       "999      10     0  119\n",
       "\n",
       "[1000 rows x 3 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = pd.DataFrame(data = np.random.randint(0,150,size = (1000,3)),\n",
    "                   columns=['Python','Math','En'])\n",
    "df3\n",
    "# 如果删掉行索引，会给一个默认的。\n",
    "# 如下"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "b08cc3a1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       49\n",
       "1       82\n",
       "2       68\n",
       "3       91\n",
       "4      107\n",
       "      ... \n",
       "995    139\n",
       "996    103\n",
       "997    121\n",
       "998    145\n",
       "999     10\n",
       "Name: Python, Length: 1000, dtype: int32"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3['Python'] # 取出的一列，是Series\n",
    "# DataFrame是由多个Series组成"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "8c58f297",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[ 49,  53, 137],\n",
       "       [ 82,  86, 106],\n",
       "       [ 68,  27,  93],\n",
       "       ...,\n",
       "       [121, 147,  44],\n",
       "       [145,   4,  36],\n",
       "       [ 10,   0, 119]])"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.values # NumPy数组，就是二维的数组"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "cd4a19ff",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>Tensorflow</th>\n",
       "      <th>Keras</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Python</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>0</td>\n",
       "      <td>38</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>13</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>3</td>\n",
       "      <td>25</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>4</td>\n",
       "      <td>23</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>5</td>\n",
       "      <td>45</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>6</td>\n",
       "      <td>26</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>7</td>\n",
       "      <td>21</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>8</td>\n",
       "      <td>30</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        index  Tensorflow  Keras\n",
       "Python                          \n",
       "39          0          38     37\n",
       "42          1          31     35\n",
       "6           2          13     49\n",
       "38          3          25      9\n",
       "33          4          23     11\n",
       "8           5          45     35\n",
       "41          6          26     16\n",
       "10          7          21     11\n",
       "42          8          30     27\n",
       "33          9           9     19"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "# SQLAlchemy是Python编程语言下的一款开源软件。提供了SQL工具包及对象关系映射（ORM）工具\n",
    "from sqlalchemy import create_engine\n",
    "df = pd.DataFrame(data = np.random.randint(0,50,size = [150,3]),# 计算机科目的考试成绩\n",
    "                   columns=['Python','Tensorflow','Keras'])\n",
    "# 数据库连接\n",
    "conn = create_engine('mysql+pymysql://root:root@localhost/pandas?charset=UTF8MB4')\n",
    "# 保存到数据库\n",
    "df.to_sql('score',#数据库中表名\n",
    "          conn,# 数据库连接\n",
    "          if_exists='append')#如果表名存在，追加数据\n",
    "# 从数据库中加载\n",
    "pd.read_sql('select * from score limit 10', # sql查询语句\n",
    "            conn, # 数据库连接\n",
    "            index_col='Python') # 指定行索引名\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0faa5248",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
